﻿using Apps.Excel;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using WebApplication2.Utility;

namespace WebApplication2.Controllers
{
    public class FileOperationController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 获取导出Excel需要的数据源;模拟从Service中返回数据
        /// </summary>
        /// <returns></returns>
        private List<ExcelDataResource> GetExcelSheetData(int id)
        {

            List<object> objlist = new List<object>();
            for (int i = 0; i < 100; i++)
            {
                objlist.Add(new UserInfo()
                {
                    UserId = i + 1,
                    UserName = $"名称-{i}",
                    Age = i + i + 1,
                    UserType = i + 1,
                    Description = $"描述_描述_描述_描述_描述_描述_描述_描述_描述_描述_{i}"
                });
            }
            List<object> Classobjlist = new List<object>();

            for (int i = 0; i < 200; i++)
            {
                Classobjlist.Add(new ClassInfo()
                {
                    ClassId = i + 1,
                    ClassName = $"名称-{i}",
                });
            }
            List<ExcelDataResource> datalist = new List<ExcelDataResource>()
                    {
                         new ExcelDataResource(){
                                SheetName="页签1",
                                TitleIndex=1,
                                SheetDataResource=objlist
                         },
                         new ExcelDataResource(){
                                SheetName="页签2",
                                TitleIndex=1,
                                SheetDataResource=Classobjlist
                         }
                    };

            return datalist;
        }

        /// <summary>
        /// 通过扩展IActionResult--ExcelResult导出Excel文件
        /// 1.生成数据
        /// 2.通过数据生成Excel
        /// 3.Excel转换成byte[]
        /// 4.写入文件流 HttpResponse 
        /// </summary>
        /// <param name="id"></param>
        public void ImportExcelFileOnWriteResponse(int id)
        {
            string ext = ".xlsx";
            new FileExtensionContentTypeProvider().Mappings.TryGetValue(ext, out string? contenttype);

            ////设置ContentType
            HttpContext.Response.ContentType = contenttype!;
            ////HttpContext.Response.ContentType = "application/vnd.ms-excel";
            ////生成文件名
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff");
            ////设置Excel文件名
            HttpContext.Response.Headers.Add("Content-Disposition", $"attachment;filename={fileName}.xlsx");


            //获取导出Excel需要的数据源
            List<ExcelDataResource> excelDataResources = GetExcelSheetData(id);
            byte[] bt = ExcelOperationHelper.ToExcelByteArray(excelDataResources);
            HttpContext.Response.BodyWriter.WriteAsync(bt);
        }

        /// <summary>
        /// 1.生成数据
        /// 2.通过数据生成Excel
        /// 3.Excel转换成byte[]
        /// 4.调用系统内置的方法File
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IActionResult ImportExcelFileOnFileMethod(int id)
        {
            List<ExcelDataResource> excelDataResources = GetExcelSheetData(id);
            byte[] bt = ExcelOperationHelper.ToExcelByteArray(excelDataResources);
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff");

            //return File(bt, "application/vnd.ms-excel", $"{fileName}.xls");
            string ext = ".xlsx";
            new FileExtensionContentTypeProvider().Mappings.TryGetValue(ext, out string? contenttype);
            return File(bt, contenttype!, $"{fileName}.{ext}");
        }

        /// <summary>
        /// 1.生成数据
        /// 2.通过数据生成Excel
        /// 3.Excel转换成byte[]
        /// 4.调用IActionResult的扩展--ExcelResult
        /// </summary>
        /// <param name="id"></param>
        public IActionResult ImportExcelFileOnActionResultExtend(int id)
        {
            // 获取导出Excel需要的数据源
            List<ExcelDataResource> list = GetExcelSheetData(id);
            return new ExcelResult(list); //调用IActionResult的扩展返回Excel
        }

        /// <summary>
        /// 文件导入
        /// </summary>
        /// <returns></returns>
        public IActionResult ImportExcelOnFormSubmit()
        {
            //获取上传的Excel文件
            IFormFile file = Request.Form.Files["file"];
            if (file != null && file.Length > 0)
            {
                string suffixName = Path.GetExtension(file.FileName).ToLower();
                if (suffixName != ".xls" && suffixName != ".xlsx")
                {
                    return Content("请导入文件为Excel格式");
                }

                //错误提示：Wrong Local header signature: 0xE011CFD0
                //导入Excel时出现错误，错误提示：Wrong Local header signature: 0xE011CFD0，这个是excel的扩展名问题，
                //.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file); 而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);

                //解决办法：
                //需直接定义.xlsx文件，不能用其他格式的文件改成.xlsx格式，
                //比如你拿个a.xls改成a.xlsx就会报错Wrong Local header signature

                //HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(file.OpenReadStream());

                try
                {

                    IWorkbook workbook;
                    if (suffixName == ".xls")
                    {
                        workbook = new HSSFWorkbook(file.OpenReadStream());
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(file.OpenReadStream());
                    }

                    List<DataTable> datatableList = ExcelOperationHelper.ToExcelDataTable(workbook);
                    ViewBag.Info = Newtonsoft.Json.JsonConvert.SerializeObject(datatableList);
                }
                catch (ICSharpCode.SharpZipLib.Zip.ZipException) //抛该异常可能是.txt文件后缀改成了.xlsx
                {
                    return Content("请导入文件为Excel格式");
                }
            }
            else
            {
                ViewBag.Info = "请上传文件";
            }
            return View();

        }


        /// <summary>
        /// JS导入
        /// </summary>
        /// <returns></returns>
        public IActionResult JsFileView()
        {
            return View();
        }

        /// <summary>
        /// 支持JS上传Excel解析
        /// </summary>
        /// <returns></returns>
        public JsonResult JSImportExcel()
        {
            //获取上传的Excel文件
            IFormFileCollection filecollection = Request.Form.Files;
            if (filecollection != null && filecollection.Count() == 0)
            {
                return new JsonResult(new 
                {
                    Success=false,
                    Message="请选择文件"
                });
            }

            var file = filecollection[0];//这里只取第一个文件
            string suffixName = Path.GetExtension(file.FileName).ToLower();

            if (suffixName != ".xls" && suffixName != ".xlsx")
            {
                return new JsonResult(new
                {
                    Success = false,
                    Message = "文件格式不对"
                });
            }

            IWorkbook workbook;
            if (suffixName == ".xls")
            {
                workbook = new HSSFWorkbook(file.OpenReadStream());
            }
            else
            {
                workbook = new XSSFWorkbook(file.OpenReadStream());
            }

            List<DataTable> datatableList = ExcelOperationHelper.ToExcelDataTable(workbook); 
            return new JsonResult(new
            {
                Success = true,
                Message = "导入成功",
                Data = Newtonsoft.Json.JsonConvert.SerializeObject(datatableList)
            });

        }


    }
}
